﻿
using CNative.Dapper.Utils;
using CNative.Utilities;
using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace CNative.Dapper.Utils
{
    /// <summary>
    /// 数据库操作抽象帮助类
    /// </summary>
    public class DbHelper : IDbHelper
    {
        #region var
        private string _dbName;
        /// <summary>
        /// 当前数据库连接字符串key名称
        /// </summary>
        public string DBName
        {
            get
            {
                return _dbName;
            }
        }

        private string _ConnectString = null;
        /// <summary>
        /// 当前数据库连接字符串
        /// </summary>
        public string ConnectString
        {
            get
            {
                CheckConnectString();
                return _ConnectString;
            }
            set { _ConnectString = value; }
        }

        /// <summary>
        /// 是否启用主从分离模式
        /// </summary>
        public bool IsUseMasterSlaveSeparation { get; set; } = false;
        private string _MasterConnectString = null;
        /// <summary>
        /// 主数据库连接字符串
        /// </summary>
        public string MasterConnectString
        {
            get
            {
                CheckConnectString();
                return _MasterConnectString;
            }
            set { _MasterConnectString = value; }
        }
        /// <summary>
        /// 从数据库连接字符串集合
        /// </summary>
        public List<string> SlaveConnectStrings { set; get; }
        /// <summary>
        /// 当前是否主从数据库模式
        /// </summary>
        protected virtual bool IsMasterSlaveSeparation
        {
            get
            {
                return IsUseMasterSlaveSeparation && SlaveConnectStrings != null && SlaveConnectStrings.Any();
            }
        }
        public int? CommandTimeout { get; set; }
        /// <summary>
        /// 数据库类型
        /// </summary>
        protected DatabaseType _dbType = DatabaseType.SqlServer;
        /// <summary>
        /// 数据库类型
        /// </summary>
        public DatabaseType DBType
        {
            get
            {
                CheckConnectString();
                return _dbType;
            }
        }
        /// <summary>
        /// 数据库提供者
        /// </summary>
        private BaseProvider _SqlDbProvider = null;
        /// <summary>
        /// 数据库提供者
        /// </summary>
        public BaseProvider SqlDbProvider
        {
            get
            {
                CheckConnectString();
                return _SqlDbProvider;
            }
        }
        /// <summary>
        /// 数据库提供工厂
        /// </summary>
        protected DbProviderFactory _dbProviderFactory = null;
        internal IDbTransaction _transaction = null;
        public virtual AopProvider Aop { get { return new AopProvider(this); } }
        /// <summary>
        /// 数据库提供者字典
        /// </summary>
        protected static Dictionary<string, Type> DicDbProvider = new Dictionary<string, Type>();
        /// <summary>
        /// 数据库类型字典
        /// </summary>
        //protected static Dictionary<string, DatabaseType> DicDBType = new Dictionary<string, DatabaseType>(StringComparer.OrdinalIgnoreCase);
        /// <summary>
        /// 数据库连接字典
        /// </summary>
        protected static Dictionary<string, Tuple<ConnectionStringSetting, Type>> DicDBName 
            = new Dictionary<string, Tuple<ConnectionStringSetting, Type>>(StringComparer.OrdinalIgnoreCase);
        #endregion
        #region DbHelper
        public DbHelper(string dbName)
        {
            _dbName = dbName;
        }
        static DbHelper()
        {
            //var databaseTypes = Enum.GetNames(typeof(DatabaseType));
            //foreach(var databaseTypeN in databaseTypes)
            //{
            //    if (Enum.TryParse(databaseTypeN, out DatabaseType databaseType))
            //    {
            //        DicDBType[databaseTypeN] = databaseType;
            //        DicDBType["system.data." + databaseTypeN] = databaseType;
            //    }
            //}
            DicDbProvider["System.Data.SqlClient"] = typeof(SqlServerProvider);// DatabaseType.SqlServer;
            DicDbProvider["System.Data.SqlClient2000"] = typeof(SqlServer2000Provider); //DatabaseType.SqlServer2000;
            
            DicDbProvider["System.Data.Oracle"] = typeof(OracleProvider); //DatabaseType.Oracle;
            DicDbProvider["System.Data.OracleClient" ] = typeof(OracleOracleClientDbProvider); //DatabaseType.Oracle;//依赖于oracle官方驱动，需要另外安装oracle客户端
            DicDbProvider["Oracle.DataAccess.Client"] = typeof(OracleProvider); //DatabaseType.Oracle;//Oracle数据库，官方非托管驱动，限制比较多
            DicDbProvider["Oracle.ManagedDataAccess.Client"] = typeof(OracleProvider); //DatabaseType.Oracle;//Oracle官方托管驱动,10g以下版本不支持，无任何依赖
            
            DicDbProvider["Devart.Data.Oracle"] = typeof(OracleDevartProvider);// DatabaseType.OracleDevart;
            DicDbProvider["System.Data.OracleDevart"] = typeof(OracleDevartProvider);// DatabaseType.OracleDevart;
            DicDbProvider["Devart"] = typeof(OracleDevartProvider);//DatabaseType.OracleDevart;
            //----------------------------------------------------------------------------------
            DicDbProvider["SqlServer"] = typeof(SqlServerProvider);
            DicDbProvider["MySql"] = typeof(MySqlProvider);
            DicDbProvider["Oracle"] = typeof(OracleProvider);
            DicDbProvider["Sqlite"] = typeof(SqliteProvider);
            DicDbProvider["MsAccess"] = typeof(MsAccessProvider);
//#if !NET40
            DicDbProvider["PostgreSql"] = typeof(PostgreSqlProvider);
            DicDbProvider["SqlServer2000"] = typeof(SqlServer2000Provider);

            DicDbProvider["OracleDevart"] = typeof(OracleDevartProvider);
            DicDbProvider["Oracle.Devart"] = typeof(OracleDevartProvider);
            DicDbProvider["Devart.Oracle"] = typeof(OracleDevartProvider);

            DicDbProvider["System.Data.OracleOleDb"] = typeof(OracleOleDbProvider);
            DicDbProvider["OracleOleDb"] = typeof(OracleOleDbProvider);
            DicDbProvider["Oracle.OleDb"] = typeof(OracleOleDbProvider);
            DicDbProvider["OleDb.Oracle"] = typeof(OracleOleDbProvider);
            //#endif
        }
        #endregion

        #region 开始数据库事务
        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <param name="isol">指定连接的事务锁定行为</param>
        public IDbTransaction BeginTransaction(System.Data.IsolationLevel isol = IsolationLevel.ReadCommitted)
        {
            _transaction = GetDbConnection(null).BeginTransaction(isol);
            return _transaction;
        }
        #endregion

        #region 提交数据库事务
        /// <summary>
        /// 提交数据库事务
        /// </summary>
        public void Commit()
        {
            if (_transaction != null)
            {
                _transaction.Commit();
                try
                {
                    if (_transaction.Connection != null)
                    {
                        _transaction.Connection?.Close();
                        _transaction.Connection?.Dispose();
                    }
                }
                catch { }
                _transaction.Dispose();
                _transaction = null;
            }
        }
        #endregion

        #region 回滚事务
        /// <summary>
        ///  从挂起状态回滚事务
        /// </summary>
        public void Rollback()
        {
            try
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();

                    if (_transaction.Connection != null && _transaction.Connection != null
                        && _transaction.Connection.State == ConnectionState.Open)
                    {
                        _transaction.Connection?.Close();
                        _transaction.Connection?.Dispose();
                    }
                    _transaction.Dispose();
                    _transaction = null;
                }
            }
            catch { }
        }
        #endregion

        #region protected virtual
        /// <summary>
        /// 获取一个连接
        /// </summary>
        /// <returns>返回一个合适的连接</returns>
        internal virtual IDbConnection GetDbConnection()
        {
            var connection = _dbProviderFactory.CreateConnection();
            connection.ConnectionString = ConnectString;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return connection;
        }
        internal virtual IDbConnection GetDbConnection(SqlEntity sql,bool isSelect = false)
        {
            CheckConnectString();
            CheckSQL(sql);
            //如果transaction为空，则创建一个新的连接,否则就使用transaction的连接，以实现事务
            if (_transaction == null)
            {
                SetSlaveConnectString(sql, isSelect);
                return GetDbConnection();
            }
            else
            {
                return _transaction.Connection;
            }
        }
        internal virtual void CheckSQL(SqlEntity sql)
        {
            if (DBType != DatabaseType.Oracle || sql.IsNullOrEmpty()
                 || sql.CommandType == CommandType.StoredProcedure || sql.Sql.IsNullOrEmpty()
                 || ConnectString.IsNullOrEmpty() || sql.Sql.Contains(":") != true)
                return;
            if (ConnectString.Replace(" ", "").ToUpper().Contains("Provider=MSDAORA".ToUpper()))
            {
                if (sql.Parameters != null)
                {
                    var pamns = sql.Parameters.ParameterNames;
                    pamns.ForEach(item =>
                    {
                        if (item.IsNotNullOrEmpty())
                            sql.Sql = sql.Sql.Replace(":" + item, "?");
                    });
                }
                if (sql.Parameter != null)
                {
                    if (sql.Parameter is DynamicParameters)
                    {
                        var pamns2 = (sql.Parameter as DynamicParameters).ParameterNames;
                        pamns2.ForEach(item =>
                        {
                            if (item.IsNotNullOrEmpty())
                                sql.Sql = sql.Sql.Replace(":" + item, "?");
                        });
                    }
                    else if (sql.Parameter is IEnumerable<KeyValuePair<string, object>>)
                    {
                        var pamns3 = (sql.Parameter as IEnumerable<KeyValuePair<string, object>>);
                        pamns3.ForEach(item =>
                        {
                            if (item.Key.IsNotNullOrEmpty())
                                sql.Sql = sql.Sql.Replace(":" + item.Key.NullToStr(), "?");
                        });
                    }
                    else
                    {
                        var properties = sql.Parameter.GetPropertyNames();
                        properties?.ForEach(item =>
                        {
                            if (item.IsNotNullOrEmpty())
                                sql.Sql = sql.Sql.Replace(":" + item, "?");
                        }); 
                        var fields = sql.Parameter.GetFieldNames();
                        fields?.ForEach(item =>
                        {
                            if (item.IsNotNullOrEmpty())
                                sql.Sql = sql.Sql.Replace(":" + item, "?");
                        });
                    }
                }
            }
        }
        internal virtual void CloseConnection(IDbConnection connection)
        {
            try
            {
                if (_transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (Exception err)
            {
                // throw err;
            }
        }
        #endregion

        #region Execute
        public virtual bool Execute(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql);

            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var ret = cnn.Execute(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);

                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return true;
            }
            catch (Exception e)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, e);
                throw e;
            }
        }

        public virtual bool Execute(List<SqlEntity> sqlList)
        {
            if (sqlList == null || sqlList.Count == 0) return false;
            if (sqlList.Count > 1) BeginTransaction();
            var cnn = GetDbConnection(null);
            
            var ret = 0;
            TraceInfo traceInfo = null;
            try
            {
                sqlList.ForEach(sql =>
                {
                    CheckSQL(sql);
                    traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
                    ret += cnn.Execute(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);
                    SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                });
                if (sqlList.Count > 1)
                    Commit();
                CloseConnection(cnn);
            }
            catch (Exception e)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, e);
                throw e;
            }
            return true;
        }
        #region BulkCopyData
        /// <summary>  
        /// 批量插入功能  
        /// </summary>  
        public virtual bool BulkCopyData(DataTable table,  string destinationTableName = null, int? bulkCopyTimeout = null)
        {
            return SqlDbProvider.BulkCopyData(table, destinationTableName, bulkCopyTimeout);
            //if (table == null || table.Rows.Count == 0) return false;
            //if (string.IsNullOrEmpty(destinationTableName))
            //    destinationTableName = table.TableName;
            //var cnn = GetDbConnection(null);
            //try
            //{
            //    using (var bulkCopy = BulkCopy.Create(cnn))
            //    {
            //        bulkCopy.EnableStreaming = true;
            //        bulkCopy.BatchSize = table.Rows.Count;
            //        bulkCopy.DestinationTableName = destinationTableName;
            //        if (bulkCopyTimeout != null)
            //            bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;

            //        bulkCopy.WriteToServer(table);
            //    }
            //}
            //catch (Exception e)
            //{
            //    Rollback();
            //    CloseConnection(cnn);
            //    throw e;
            //}
            //return true;
        }
        /// <summary>  
        /// 批量插入功能  
        /// </summary>  
        public virtual bool BulkCopyData<T>(List<T> entityList, string dbName="", string destinationTableName = null, int? bulkCopyTimeout = null) where T : class, new()
        {
            if (entityList == null || entityList.Count == 0) return false;
            if (string.IsNullOrEmpty(destinationTableName))
            {
                var tb = Funs.GetDbTableInfo<T>(this);
                if (tb != null && tb.TableName.IsNotNullOrEmpty())
                {
                    destinationTableName = tb.TableName;
                    dbName = tb.Schema;
                }
            }
            var table = ToSqlBulkCopyDataTable(entityList, destinationTableName, dbName);
            return BulkCopyData(table, destinationTableName, bulkCopyTimeout);
        }
        public DataTable ToSqlBulkCopyDataTable<TModel>(List<TModel> modelList,string tableName, string DbName)
        {
            DataTable dt = new DataTable();

            Type modelType = typeof(TModel);

            List<DbColumnInfo> columns = Funs.GetDbDbColumnInfo(this, tableName, DbName);
            List<PropertyInfo> mappingProps = new List<PropertyInfo>();

            var props = modelType.GetProperties();
            for (int i = 0; i < columns.Count; i++)
            {
                var column = columns[i];
                PropertyInfo mappingProp = props.FirstOrDefault(c => c.Name.Equals(column.Name, StringComparison.OrdinalIgnoreCase));
                if (mappingProp == null)
                    continue;
                    //throw new Exception(string.Format("model 类型 '{0}'未定义与表 '{1}' 列名为 '{2}' 映射的属性", modelType.FullName, tableName, column.Name));

                mappingProps.Add(mappingProp);
                Type dataType = GetUnderlyingType(mappingProp.PropertyType);
                if (dataType.IsEnum)
                    dataType = typeof(int);

                dt.Columns.Add(new DataColumn(column.Name, dataType));
            }

            foreach (var model in modelList)
            {
                DataRow dr = dt.NewRow();
                for (int i = 0; i < mappingProps.Count; i++)
                {
                    PropertyInfo prop = mappingProps[i];
                    object value = prop.FastGetValue(model);// prop.GetValue(model);

                    if (GetUnderlyingType(prop.PropertyType).IsEnum)
                    {
                        if (value != null)
                            value = (int)value;
                    }
                    dr[i] = value ?? DBNull.Value;
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
        protected Type GetUnderlyingType(Type type)
        {
            Type unType = Nullable.GetUnderlyingType(type); ;
            if (unType == null)
                unType = type;

            return unType;
        }
        #endregion
        #endregion
        #region Query
        public virtual IDataReader GetDataReader(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql, true);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var reader = cnn.ExecuteReader(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return reader;
            }
            catch (Exception err)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, err);
                throw err;
            }
        }
        public virtual DataSet QueryDataSet(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql, true);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var ds = new XDataSet();

                var reader = cnn.ExecuteReader(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);
                ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });

                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return ds;
            }
            catch (Exception err)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, err);
                throw err;
            }
        }

        public DataTable QueryDataTable(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql, true);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var dt = new DataTable();
                var reader = cnn.ExecuteReader(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);
                dt.Load(reader);
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return dt;
            }
            catch (Exception err)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, err);
                throw err;
            }
        }

        public List<T> Query<T>(SqlEntity sql) where T : class
        {
            var cnn = GetDbConnection(sql);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var ret = cnn.Query<T>(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, false, sql.CommandTimeout ?? CommandTimeout, sql.CommandType)?.ToList();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return ret;
            }
            catch (Exception e)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, e);
                throw e;
            }
        }
        public IEnumerable<dynamic> QueryMultiple(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                using (var multi = cnn.QueryMultiple(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType))
                {
                    var data = multi.Read();
                    SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                    return data;
                }
            }
            catch (Exception err)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, err);
                throw err;
            }
        }
        public Tuple<List<Entity1>, List<Entity2>> QueryMultiple<Entity1, Entity2>(SqlEntity sql)
        {
            var rets = QueryMultiple<Entity1, Entity2, object, object, object, object>(sql);
            return Tuple.Create(rets.Item1, rets.Item2);
        }
        public Tuple<List<Entity1>, List<Entity2>, List<Entity3>> QueryMultiple<Entity1, Entity2, Entity3>(SqlEntity sql)
        {
            var rets = QueryMultiple<Entity1, Entity2, Entity3, object, object, object>(sql);
            return Tuple.Create(rets.Item1, rets.Item2, rets.Item3);
        }
        public Tuple<List<Entity1>, List<Entity2>, List<Entity3>, List<Entity4>> QueryMultiple<Entity1, Entity2, Entity3, Entity4>(SqlEntity sql)
        {
            var rets = QueryMultiple<Entity1, Entity2, Entity3, Entity4, object, object>(sql);
            return Tuple.Create(rets.Item1, rets.Item2, rets.Item3, rets.Item4);
        }
        public Tuple<List<Entity1>, List<Entity2>, List<Entity3>, List<Entity4>, List<Entity5>> QueryMultiple<Entity1, Entity2, Entity3, Entity4, Entity5>(SqlEntity sql)
        {
            var rets = QueryMultiple<Entity1, Entity2, Entity3, Entity4, Entity5, object>(sql);
            return Tuple.Create(rets.Item1, rets.Item2, rets.Item3, rets.Item4, rets.Item5);
        }
        public Tuple<List<Entity1>, List<Entity2>, List<Entity3>, List<Entity4>, List<Entity5>, List<Entity6>> QueryMultiple<Entity1, Entity2, Entity3, Entity4, Entity5, Entity6>(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                List<Entity1> entity1s = new List<Entity1>();
                List<Entity2> entity2s = new List<Entity2>();
                List<Entity3> entity3s = new List<Entity3>();
                List<Entity4> entity4s = new List<Entity4>();
                List<Entity5> entity5s = new List<Entity5>();
                List<Entity6> entity6s = new List<Entity6>();
                using (var multi = cnn.QueryMultiple(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType))
                {
                    //multi.IsConsumed   reader的状态 ，true 是已经释放
                    if (!multi.IsConsumed)
                    {
                        entity1s = multi.Read<Entity1>()?.ToList();
                    }
                    if (!multi.IsConsumed)
                    {
                        entity2s = multi.Read<Entity2>()?.ToList();
                    }
                    if (!multi.IsConsumed)
                    {
                        entity3s = multi.Read<Entity3>()?.ToList();
                    }
                    if (!multi.IsConsumed)
                    {
                        entity4s = multi.Read<Entity4>()?.ToList();
                    }
                    if (!multi.IsConsumed)
                    {
                        entity5s = multi.Read<Entity5>()?.ToList();
                    }
                    if (!multi.IsConsumed)
                    {
                        entity6s = multi.Read<Entity6>()?.ToList();
                    }
                }
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return Tuple.Create(entity1s, entity2s, entity3s, entity4s, entity5s, entity6s);
            }
            catch (Exception e)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, e);
                throw e;
            }
        }
        public T QuerySingle<T>(SqlEntity sql) where T : class
        {
            var cnn = GetDbConnection(sql);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var res = cnn.QuerySingleOrDefault<T>(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return res;
            }
            catch (Exception err)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, err);
                throw err;
            }
        }
        #endregion
        #region GetSingle
        public virtual T GetSingle<T>(SqlEntity sql)
        {
            var cnn = GetDbConnection(sql);
            var traceInfo = SqlMapperTrace.ShellBeforeCommandExecute(sql);
            try
            {
                var res = cnn.ExecuteScalar<T>(sql.Sql, sql.Parameters ?? sql.Parameter, _transaction, sql.CommandTimeout ?? CommandTimeout, sql.CommandType);
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo);
                return res;
            }
            catch (Exception err)
            {
                Rollback();
                CloseConnection(cnn);
                SqlMapperTrace.ShellAfterCommandExecute(traceInfo, err);
                throw err;
            }
        }
        #endregion

        #region Dispose
        /// <summary>
        /// 标记DbContext是否已经释放
        /// </summary>
        protected bool IsDisposed { get; set; }
        /// <summary>
        /// 释放数据,初始化状态
        /// </summary>
        public void Dispose()
        {
            IsDisposed = true;
            Commit();
        }
        #endregion

        #region CreateSqlEntity
        public SqlEntity CreateSqlEntity()
        {
            return new SqlEntity(this);
        }
        public SqlEntity CreateSqlEntity(string sql)
        {
            return new SqlEntity(this) { Sql = sql };
        }
        #endregion

        #region CheckConnectString/GetConnectString
        protected virtual void CheckConnectString()
        {
            if (_SqlDbProvider == null || _ConnectString == null || _ConnectString == ""
                || _MasterConnectString == null || _MasterConnectString == "")
                SetConnectString(_dbName);
        }
        /// <summary>
        /// 通过连接名或连接字符串获取连接字符串
        /// </summary>
        /// <param name="nameOrconStr">连接名或者连接字符串</param>
        /// <returns></returns>
        protected virtual void SetConnectString(string DBname)
        {
            ConnectionStringSetting configSettings = null;
            if (DicDBName.TryGetValue(DBname, out Tuple<ConnectionStringSetting, Type> dbName))
            {
                configSettings = dbName.Item1;
                _SqlDbProvider = dbName.Item2.FastInstance<BaseProvider>(this);
            }
            if (configSettings == null)
            {
                if (CNative.Utilities.ConfigurationHelper.ConnectionStrings == null || CNative.Utilities.ConfigurationHelper.ConnectionStrings.Count == 0)
                    throw new Exception("appsettings.json文件中未找到[ConnectionStrings]配置节点");
                if (!CNative.Utilities.ConfigurationHelper.ConnectionStrings.ContainsKey(DBname))
                    throw new Exception("未找到名称为[" + DBname + "]的连接字符串");

                configSettings = CNative.Utilities.ConfigurationHelper.ConnectionStrings[DBname];
                if (configSettings == null)
                    throw new Exception("未找到名称为" + DBname + "的数据库配置节点");
            }
            _MasterConnectString = configSettings.ConnectionString;
            _ConnectString = _MasterConnectString;
            CommandTimeout = configSettings.CommandTimeout;

            if (_SqlDbProvider == null)
                _SqlDbProvider = GetDbProvider(configSettings.ProviderName);
            _dbType = _SqlDbProvider.DBType;
            _dbProviderFactory = _SqlDbProvider.DbProviderFactory;

            InitSlaveConnectStrings(configSettings);

            DicDBName[DBname] = Tuple.Create(configSettings, _SqlDbProvider.GetType());
        }
        /// <summary>
        /// 获取提供工厂
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        protected virtual BaseProvider GetDbProvider(string providerName)
        {
            BaseProvider provider = null;
            if (DicDbProvider.TryGetValue(providerName, out Type providerType))
            {
                provider = providerType.FastInstance<BaseProvider>(this);
                if (provider == null)
                    throw new Exception("实例化[" + providerType + "]数据库提供者时失败");
            }
            else
            {
                throw new Exception($"未实现[{providerName.ToString()}]数据库提供者");
            }
            return provider;
        }
        #endregion
        #region AddDbProvider
        /// <summary>
        /// 添加数据库提供者
        /// </summary>
        /// <param name="dbType">SqlServer</param>
        /// <param name="providerType">typeof(SqlServerProvider)</param>
        /// <returns></returns>
        public static bool AddDbProvider(string providerName, Type providerType)
        {
            if (DicDbProvider == null) DicDbProvider = new Dictionary<string, Type>();
            DicDbProvider[providerName] = providerType;
            return true;
        }
        /// <summary>
        /// 添加数据库提供者
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="providerType"></param>
        /// <returns></returns>
        public static bool AddDbProvider(string providerName, string providerType)
        {
            if (DicDbProvider == null) DicDbProvider = new Dictionary<string, Type>();
            DicDbProvider[providerName] = Type.GetType(providerType);
            return true;
        }
        ///// <summary>
        ///// 添加数据库类型对应关系
        ///// </summary>
        ///// <param name="dbTypeStr">System.Data.SqlClient</param>
        ///// <param name="dbType">SqlServer</param>
        ///// <returns></returns>
        //public static bool AddSqlDbTypeMapping(string dbTypeStr, DatabaseType dbType)
        //{
        //    if (dbTypeStr.IsNullOrEmpty()) return false;
        //    if (DicDBType == null) DicDBType = new Dictionary<string, DatabaseType>(StringComparer.OrdinalIgnoreCase);
        //    DicDBType[dbTypeStr] = dbType;
        //    return true;
        //}
        ///// <summary>
        ///// 添加数据库类型和提供者对应关系
        ///// </summary>
        ///// <param name="dbTypeStr">System.Data.SqlClient</param>
        ///// <param name="dbType">SqlServer</param>
        ///// <param name="providerType">typeof(SqlServerProvider)</param>
        ///// <returns></returns>
        //public static bool AddSqlDbProviderMapping(string dbTypeStr, DatabaseType dbType, Type providerType)
        //{
        //    AddSqlDbTypeMapping(dbTypeStr, dbType);
        //    return AddSqlDbProvider(dbType, providerType);
        //}
        #endregion

        //---------------------------------------------------------------------------------------------------------
        #region Ping
        /// <summary>
        /// 判断当前连接字符串是否有效
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="isThrow"></param>
        /// <returns></returns>
        public virtual bool Ping(string connectionString = "", bool isThrow = false)
        {
            if (connectionString.IsNullOrEmpty())
                connectionString = this.ConnectString;

            var connection = _dbProviderFactory.CreateConnection();
            connection.ConnectionString = connectionString;
            if (connection.State != ConnectionState.Open)
                connection.Open();

            return Helpers.DbConnectionExtensions.Ping(connection, this.SqlDbProvider.FullSqlDateNow, isThrow);
        }
        #endregion

        #region SetSlaveConnectString
        /// <summary>
        /// 初始化从数据库连接集合
        /// </summary>
        /// <param name="connectionStringSetting"></param>
        protected void InitSlaveConnectStrings(ConnectionStringSetting connectionStringSetting)
        {
            if (connectionStringSetting.IsNullOrEmpty() || connectionStringSetting.SlaveConnectStrings.IsNullOrEmpty_())
                return;

            this.IsUseMasterSlaveSeparation = connectionStringSetting.IsUseMasterSlaveSeparation;

            if (SlaveConnectStrings == null) SlaveConnectStrings = new List<string>();
            SlaveConnectStrings.Clear();
            foreach (var DBname in connectionStringSetting.SlaveConnectStrings)
            {
                try
                {
                    if (DBname.IsNullOrEmpty())
                        continue;

                    var ConnStr = DBname;
                    var css = CNative.Utilities.ConfigurationHelper.ConnectionStrings[DBname];
                    if (css != null && css.ConnectionString.IsNotNullOrEmpty())
                    {
                        ConnStr = css.ConnectionString;
                    }
                    if (!SlaveConnectStrings.Any(a => EqualsConnectionString(a, ConnStr))
                        && Ping(ConnStr))
                    {
                        SlaveConnectStrings.Add(ConnStr);
                    }
                }
                catch { }
            }
        }
        /// <summary>
        /// 设置从数据库连接
        /// </summary>
        /// <param name="sql"></param>
        protected virtual void SetSlaveConnectString(SqlEntity sql, bool isSelect = false)
        {
            if (isSelect && sql != null && sql.CommandType == CommandType.Text && this._transaction == null 
                && this.IsMasterSlaveSeparation && IsRead(sql.Sql))
            {
                var saves = this.SlaveConnectStrings;
                var currentIndex = saves.GetRandomIndex();
                _ConnectString = saves[currentIndex];
            }
            else
            {
                _ConnectString = _MasterConnectString;
            }
        }

        protected virtual bool IsRead(string sql)
        {
            var sqlLower = sql.ToLower();
            var result = System.Text.RegularExpressions.Regex.IsMatch(sqlLower, "[ ]*select[ ]") && !System.Text.RegularExpressions.Regex.IsMatch(sqlLower, "[ ]*insert[ ]|[ ]*update[ ]|[ ]*delete[ ]");
            return result;
        }
        protected virtual bool EqualsConnectionString(string connectionString1, string connectionString2)
        {
            if (connectionString1.IsNullOrEmpty() || connectionString2.IsNullOrEmpty())
                return false;

            var connectionString1Array = connectionString1.Split(';');
            var connectionString2Array = connectionString2.Split(';');
            var result = connectionString1Array.Except(connectionString2Array);
            return result.Count() == 0;
        }
        #endregion
    }

    #region internal class XLoadAdapter /internal  class XDataSet
    /// <summary>
    /// 数据适配器，扩展Fill方法
    /// .NET的DataSet.Load方法，底层调用DataAdapter.Fill(DataTable[], IDataReader, int, int)
    /// Dapper想要返回DataSet，需要重写Load方法，不必传入DataTable[]，因为数组长度不确定
    /// </summary>
    internal class XLoadAdapter : DataAdapter
    {
        /// <summary>
        /// 数据适配器
        /// </summary>
        public XLoadAdapter()
        {
        }

        /// <summary>
        /// 读取dataReader
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="dataReader"></param>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords)
        {
            return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
        }
    }

    /// <summary>
    /// 扩展Load方法
    /// </summary>
    internal class XDataSet : DataSet
    {
        /// <summary>
        /// Dapper想要返回DataSet，需要重写Load方法
        /// </summary>
        /// <param name="reader">IDataReader</param>
        /// <param name="loadOption">LoadOption</param>
        /// <param name="handler">FillErrorEventHandler</param>
        /// <param name="tables">DataTable</param>
        public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)
        {
            XLoadAdapter adapter = new XLoadAdapter
            {
                FillLoadOption = loadOption,
                MissingSchemaAction = MissingSchemaAction.AddWithKey
            };
            if (handler != null)
            {
                adapter.FillError += handler;
            }
            adapter.FillFromReader(this, reader, 0, 0);
            if (!reader.IsClosed && !reader.NextResult())
            {
                reader.Close();
            }
        }
    }
    #endregion
}
